Nested Transactions

use ReportBuilderTraining

---
--- Experiment with nested transactions
---

---
--- setup a temp table to test with
---

if object_id('tempdb..##temp') is not null
   	drop table ##temp
 
go

create table ##temp (ID int IDENTITY(1,1) NOT NULL, Name varchar(50))
go

insert into ##temp (Name) values ('Ed')
go

select *
	from ##temp
go


---
--- experiment with "nested" transacions
---
--- nested transactions come up when a stored procedure that contains transactions is called from a
--- context inside another transaction
---

begin transaction
	select @@TRANCOUNT

	insert into ##temp (Name) values ('Kim')
	select * from ##temp

	-- begin "nested transaction"
	begin transaction
		select @@TRANCOUNT

		insert into ##temp (Name) values ('Sam')
		select * from ##temp

	-- ROLLBACK rolls back to first BEGIN
	rollback transaction 
	select @@TRANCOUNT

	select * from ##temp

-- error, no transactions
select XACT_STATE()
commit transaction


select *
	from ##temp





---
--- partial rollbacks with SAVE
---

begin transaction
	select @@TRANCOUNT

	insert into ##temp (Name) values ('Kim')
	select * from ##temp

	-- begin "nested transaction", create savepoint first
	save transaction testSavePoint
	begin transaction
		select @@TRANCOUNT

		insert into ##temp (Name) values ('Sam')
		select * from ##temp

	-- ROLLBACK rolls back to our savepoint
	rollback transaction testSavePoint
	select @@TRANCOUNT

	commit transaction
	select @@TRANCOUNT

	select * from ##temp

commit transaction
select @@TRANCOUNT

select * from ##temp